import sqlalchemy as sa
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy import orm

import logging

# To watch the DB activity:
logging.basicConfig(format='%(message)s')
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)


engine = sa.create_engine('sqlite://')
metadata = sa.MetaData()

t_users = sa.Table("users", metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("firstname", sa.types.String(100), nullable=False))

t_rooms = sa.Table("rooms", metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("label", sa.types.String(100), nullable=False))

t_roomsusers = sa.Table('roomsusers', metadata,
    sa.Column('user_id', sa.types.Integer, sa.ForeignKey('users.id'), 
              primary_key=True),
    sa.Column('room_id', sa.types.Integer, sa.ForeignKey('rooms.id'), 
              primary_key=True),
    sa.Column('from_date', sa.types.Date),
    sa.Column('to_date', sa.types.Date))


for table in (t_users, t_rooms, t_roomsusers):
  table.create(bind=engine)



class Room(object):
  users = association_proxy('userassociations', 'user',
                            creator=lambda x, **kw:RoomUser(user=x, **kw))
  def __init__(self, label):
    self.label = label
  def __repr__(self):
    return "<Room %s>" % self.label


class User(object):
  rooms = association_proxy('roomassociations', 'room', 
                            creator=lambda x, **kw:RoomUser(room=x, **kw))
  def __init__(self, firstname):
    self.firstname = firstname
  def __repr__(self):
    return "<User %s>" % self.firstname


class RoomUser(object):
  def __init__(self, room=None, user=None, from_date=None, to_date=None):
    assert not room or not user
    if room: self.room = room
    if user: self.user = user
    self.from_date = from_date
    self.to_date = to_date
    
  def __repr__(self):
    return "<UserRoomAssociation %s %s>" % (self.room, self.user)

orm.mapper(User, t_users, properties={
  'roomassociations': orm.relation(RoomUser, cascade="all, delete-orphan")
                                      })

orm.mapper(Room, t_rooms, properties={
  'userassociations': orm.relation(RoomUser, cascade="all, delete-orphan")})

orm.mapper(RoomUser, t_roomsusers, properties={
    'user': orm.relation(User, lazy=False),
    'room': orm.relation(Room, lazy=False),
})


connection = engine.connect()
trans = connection.begin()

Session = orm.sessionmaker(bind=engine, transactional=False)
session = Session()
session.begin()

toto = User("toto")
session.add(toto)
print "user toto:", toto
titi = User("titi")
session.add(titi)
print "user titi:", titi
ch = Room("2028")
session.add(ch)
print "ch 20028:", ch

session.flush()

print "toto.rooms.append(ch)"
toto.rooms.append(ch)

session.flush()
print "toto.rooms:", toto.rooms
print "ch.users", ch.users

session.commit()

print toto.rooms
print ch.users

print
print "ch.users.append(titi, date=None)"
ch.users.append(titi, from_date=None)
session.flush()
print "ch.users", ch.users
print "titi.rooms:", titi.rooms
